package cn.edu.nju.software.paymentSystem;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBAccessor {

	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String TABLE_USER = "tuser";
	private static final String TABLE_PAYMENT = "tpayment";
	private static final String URL = "jdbc:mysql://192.168.1.228:3306/systemb";

	private static final String username = "systemc";
	private static final String password = "systemc";

	private Connection connection;
	private Statement statement;

	public DBAccessor() {
		this("systemc", "systemc");
	}

	public DBAccessor(String username, String password) {
		try {
			initStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void initStatement() throws ClassNotFoundException, SQLException {
		Class.forName(DRIVER);
		connection = DriverManager.getConnection(URL, username, password);
		statement = connection.createStatement();
	}

	public int getBalance(String user, String password) throws SQLException {
		String sqlCommand = "SELECT ballance FROM " + TABLE_USER
				+ " WHERE name=\"" + user + "\" AND password=\"" + password
				+ "\"";
		ResultSet rs = statement.executeQuery(sqlCommand);
		if (rs.next()) {
			return rs.getInt(1);
		}
		return -1;
	}

	public boolean paymentFrom(String user, String password, int money,
			String purchaseNo, String seller) throws SQLException {
		int ballance = getBalance(user, password);
		if (ballance < money) {
			return false;
		}
		String sqlCommand = "SELECT COUNT(*) FROM " + TABLE_PAYMENT
				+ " WHERE purchaseNo=\"" + purchaseNo + "\"";
		ResultSet rs = statement.executeQuery(sqlCommand);
		rs.next();
		if (rs.getInt(1) >= 1) {
			return false;
		}
		sqlCommand = "INSERT INTO " + TABLE_PAYMENT + " VALUES (\'"
				+ purchaseNo + "\', \'" + money + "\', \'" + user + "\', \'"
				+ seller + "\')";
		statement.executeUpdate(sqlCommand);
		sqlCommand = "UPDATE " + TABLE_USER + " SET ballance=ballance-" + money
				+ " WHERE name=\"" + user + "\" AND password=\"" + password
				+ "\"";
		statement.executeUpdate(sqlCommand);
		return true;
	}

	public void paymentTo(String purchaseNo, boolean succeed)
			throws SQLException {
		String theOne = succeed ? "toU" : "fromU";
		String sqlCommand = "SELECT " + theOne + ", money FROM "
				+ TABLE_PAYMENT + " WHERE purchaseNo=\"" + purchaseNo + "\"";
		ResultSet rs = statement.executeQuery(sqlCommand);
		String name = "";
		int money = 0;
		if (rs.next()) {
			name = rs.getString(1);
			money = rs.getInt(2);
		}
		sqlCommand = "UPDATE " + TABLE_USER + " SET ballance=ballance+" + money
				+ " WHERE name=\"" + name + "\"";
		statement.executeUpdate(sqlCommand);
		sqlCommand = "DELETE FROM " + TABLE_PAYMENT + " WHERE purchaseNo=\""
				+ purchaseNo + "\"";
		statement.executeUpdate(sqlCommand);
	}

}
